package org.dawnsql.stock.controller;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.internal.LinkedTreeMap;
import com.google.gson.reflect.TypeToken;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Controller
public class MyStockMController {
    Gson gson = new GsonBuilder()
            .enableComplexMapKeySerialization()
            .setDateFormat("yyyy-MM-dd HH:mm:ss")
            .create();

    Connection conn = null;
    public MyStockMController() throws ClassNotFoundException, SQLException {
        Class.forName("org.h2.Driver");
        this.conn = DriverManager.getConnection("jdbc:h2:~/data;AUTO_SERVER=TRUE", "root", "root");
    }

    @RequestMapping(value = "/my_stock_month")
    public String my_stock(ModelMap model, HttpServletRequest request, HttpServletResponse response)
    {
        return "my_stock_month";
    }

    @RequestMapping(value = "/to_db_m")
    public @ResponseBody
    String to_db_m(ModelMap model, HttpServletRequest request,
                 HttpServletResponse response,
                 @RequestParam(value = "symbol", required = false) String symbol,
                 @RequestParam(value = "code", required = false) String code) throws UnsupportedEncodingException, SQLException {

        try {
            toDb_m(this.conn, code, symbol);
            System.out.println(symbol);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return "1";
    }

    public void toDb_m(Connection conn, final String line, String symbol) throws SQLException {
        Pattern pattern = Pattern.compile("\\(([^)]+)\\)");
        Matcher matcher = pattern.matcher(line);

        if (matcher.find()) {
            String result = matcher.group(1); // 获取第一个捕获组内容

            LinkedTreeMap m = this.gson.fromJson(result, new TypeToken<LinkedTreeMap>() {}.getType());
            LinkedTreeMap data = (LinkedTreeMap) m.get("data");
            List klines = (List) data.get("klines");
            String name = (String) data.get("name");

            for (int i = klines.size() - 1; i >= klines.size() - 15; i--) {
                System.out.println(klines.get(i).toString());
                String dl = klines.get(i).toString();
                String[] vs = dl.split(",");

                if (hasUpdate_m(conn, symbol, vs[0]) == false) {
                    // "2025-05-20,11.40,11.39,11.47,11.37,643635,734455265.82,0.88,0.18,0.02,0.33"
                    /**
                     * 2025-05-23,20.59,19.54,20.59,19.43,1152762,2290193499.00,5.64,-4.96,-1.02,8.13
                     * price = vs[4]
                     * zhangdie = vs[8]
                     * zhenfu = vs[7]
                     * huanshoulv = vs[10]
                     * stock_date = vs[0]
                     * */
                    updateDate_m(conn, symbol, name, vs[4], vs[8], vs[7], vs[10], vs[0]);
                } else {
                    break;
                }
            }
        }
    }

    /**
     * PK id
     * */
    public Long getPkId_m(Connection conn) {
        String sql = "select count(id) from df_stock_month";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        long id = 0L;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                id = rs.getLong(1);
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return id;
    }

    /**
     * 判断 symbol 的数量是否是 20，如果不是 20，就修改最后一项的数据
     * 2025-05-23,20.59,19.54,20.59,19.43,1152762,2290193499.00,5.64,-4.96,-1.02,8.13
     * price = vs[4]
     * zhangdie = vs[8]
     * zhenfu = vs[7]
     * huanshoulv = vs[10]
     * stock_date = vs[0]
     * */
    public void updateDate_m(Connection conn, String symbol, String name, String price, String zhangdie, String zhenfu, String huanshoulv, String stock_date) throws SQLException {
        Long symbolCount = getSymbolCount_m(conn, symbol);
        if (symbolCount <= 15) {
            Statement stmt = null;

            if (symbolCount < 15) {
                try {
                    Long id = getPkId_m(conn) + 1;
                    String sql = "insert into df_stock_month (id, symbol, name, price, zhangdie, zhenfu, huanshoulv, stock_date) values (" + id + ", '" + symbol + "', '" + name + "', " + price + ", " + zhangdie + ", " + zhenfu + ", " + huanshoulv + ", '" + stock_date + "')";
                    //cache.query(new SqlFieldsQuery(sql)).getAll();
                    stmt = conn.createStatement();
                    stmt.execute(sql);
                } catch (Exception e) {
                    // 修改最后一项的数据
                    Long pkid = getPkIdStock_date_w(conn, symbol, stock_date);
                    String sql = "update df_stock_month SET price = "+ price +", zhangdie = "+ zhangdie +", zhenfu = "+ zhenfu +", huanshoulv = "+ huanshoulv +", stock_date = '"+ stock_date +"' WHERE id = " + pkid.toString();
                    //cache.query(new SqlFieldsQuery(sql)).getAll();
                    stmt = conn.createStatement();
                    stmt.execute(sql);
                }
            } else {
                // 修改最后一项的数据
                Long pkid = getSymbolLastId_m(conn, symbol);
                String sql = "update df_stock_month SET price = "+ price +", zhangdie = "+ zhangdie +", zhenfu = "+ zhenfu +", huanshoulv = "+ huanshoulv +", stock_date = '"+ stock_date +"' WHERE id = " + pkid.toString();
                //cache.query(new SqlFieldsQuery(sql)).getAll();
                stmt = conn.createStatement();
                stmt.execute(sql);
            }
        }
    }

    /**
     * PK id
     * */
    public Long getPkIdStock_date_w(Connection conn, String symbol, String stock_date) {
        String sql = "select id from df_stock_month where symbol = '"+ symbol +"' and stock_date = '"+ stock_date +"'";

        PreparedStatement stmt = null;
        ResultSet rs = null;
        long id = 0L;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                id = rs.getLong(1);
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return id;
    }

    /**
     * 获取 Symbol 的个数
     * */
    public Long getSymbolLastId_m(Connection conn, String symbol) {
        String sql = "select id from df_stock_month where symbol = '"+ symbol +"' ORDER BY stock_date asc LIMIT 0, 1";
        PreparedStatement stmt = null;
        ResultSet rs = null;
        long id = 0L;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                id = rs.getLong(1);
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return id;
    }

    /**
     * 获取 Symbol 的个数
     * */
    public Long getSymbolCount_m(Connection conn, String symbol) {
        String sql = "select count(id) from df_stock_month where symbol = '"+ symbol +"'";
        PreparedStatement stmt = null;
        ResultSet rs = null;
        long id = 0L;

        try {
            stmt = conn.prepareStatement(sql);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                id = rs.getLong(1);
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return id;
    }

    public boolean hasUpdate_m(Connection conn, String symbol, String stock_date) {

        String sql = "select symbol from df_stock_month where symbol = ? and stock_date = '"+ stock_date +"'";
        PreparedStatement stmt = null;
        ResultSet rs = null;
        boolean id = false;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, symbol);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            // 6. 遍历结果集并处理数据
            while (rs.next()) {
                id = true;
                break;
            }

        } catch (SQLException e) {
            System.err.println("数据库操作失败！错误信息：" + e.getMessage());
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return id;
    }
}














































